package org.kjtc.exportService;

import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.kjtc.balanceEntity.ChargingStation;
import org.kjtc.balanceEntity.LineBalance;
import org.kjtc.util.DoubleUtil;
import org.kjtc.util.Tools;

@SuppressWarnings({ "deprecation", "unused" })
public class ExportAllLineBalance {

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public void exportExcel(String[] headers, List<LineBalance> list, ServletOutputStream servletOutputStream, double allTotalChargingKWH, double totalChargingPrice, double totalServicePrice, double totalPrice) throws Exception { 
		// 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        
        HSSFSheet sheetTotal = workbook.createSheet("统计");
        
        HSSFDataFormat df = workbook.createDataFormat();
        
        sheetTotal.setColumnWidth(0, 5000);
        sheetTotal.setColumnWidth(1, 5000);
        sheetTotal.setColumnWidth(2, 5000);
        sheetTotal.setColumnWidth(3, 5000);
        
        sheetTotal.setMargin(HSSFSheet.TopMargin,( double ) 0.0); // 上边距
        sheetTotal.setMargin(HSSFSheet.BottomMargin,( double ) 0.0 ); // 下边距
        sheetTotal.setMargin(HSSFSheet.LeftMargin,( double ) 0.0 ); // 左边距
        sheetTotal.setMargin(HSSFSheet.RightMargin,( double ) 0.0 ); // 右边距
        
        HSSFCellStyle style_total = workbook.createCellStyle();
        style_total.setDataFormat(df.getFormat("##0.00000000"));
        // 设置这些样式
        style_total.setFillForegroundColor(HSSFColor.WHITE.index);
        style_total.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style_total.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_total.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style_total.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_total.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置自动换行
        style_total.setWrapText(true);
        // 设置文字水平居中
        style_total.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置文字垂直居中
        style_total.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 生成一个字体
        HSSFFont font_total = workbook.createFont();
        font_total.setColor(HSSFColor.AUTOMATIC.index);
        font_total.setFontName("Arial");
        font_total.setFontHeightInPoints((short) 10);
        // 不加粗
        font_total.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 把字体应用到当前的样式  
        style_total.setFont(font_total);

        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.WHITE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setWrapText(true);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.AUTOMATIC.index);
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 10);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        workbook.setRepeatingRowsAndColumns(0, 0, headers.length - 1, 0, 2);
        
        HSSFCellStyle style_data = workbook.createCellStyle();
        style_data.setFillForegroundColor(HSSFColor.WHITE.index);
        style_data.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style_data.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style_data.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style_data.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style_data.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style_data.setWrapText(true);
        style_data.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_data.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont font_data = workbook.createFont();
        font_data.setColor(HSSFColor.AUTOMATIC.index);
        font_data.setFontName("Arial");
        font_data.setFontHeightInPoints((short) 10);
        font_data.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        style_data.setFont(font_data);
        
		HSSFCellStyle cellStyle_2 = workbook.createCellStyle();
		cellStyle_2.setDataFormat(df.getFormat("##0.00"));
		cellStyle_2.setFillForegroundColor(HSSFColor.WHITE.index);
		cellStyle_2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		cellStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle_2.setWrapText(true);
		cellStyle_2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		cellStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFCellStyle cellStyle_0 = workbook.createCellStyle();
		cellStyle_0.setDataFormat(df.getFormat("##0"));
		cellStyle_0.setFillForegroundColor(HSSFColor.WHITE.index);
		cellStyle_0.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		cellStyle_0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle_0.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle_0.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle_0.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle_0.setWrapText(true);
		cellStyle_0.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		cellStyle_0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		
		HSSFCellStyle cellStyle_4 = workbook.createCellStyle();
		cellStyle_4.setDataFormat(df.getFormat("##0.0000"));
		cellStyle_4.setFillForegroundColor(HSSFColor.WHITE.index);
		cellStyle_4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		cellStyle_4.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		cellStyle_4.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cellStyle_4.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cellStyle_4.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cellStyle_4.setWrapText(true);
		cellStyle_4.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		cellStyle_4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        
        HSSFCellStyle cellStyle1 = workbook.createCellStyle();

        HSSFDataFormat format= workbook.createDataFormat();

        cellStyle1.setDataFormat(format.getFormat("yyyy-MM-dd hh:mm:ss"));
        cellStyle1.setFillForegroundColor(HSSFColor.WHITE.index);
        cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle1.setWrapText(true);
        cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle1.setFont(font_data);
        
        HSSFCellStyle style_title = workbook.createCellStyle();
        style_title.setFillForegroundColor(HSSFColor.WHITE.index);
        style_title.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style_title.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style_title.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont font_title = workbook.createFont();  
        font_title.setColor(HSSFColor.AUTOMATIC.index);  
        font_title.setFontHeightInPoints((short) 26);
        font_title.setFontName("Arial");
        font_title.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style_title.setFont(font_title);
        
        HSSFRow totalRow = sheetTotal.createRow(0);
        HSSFCell cell_total = totalRow.createCell(0);
        cell_total.setCellStyle(style_total);
        cell_total.setCellValue("总电量");
        
        cell_total = totalRow.createCell(1);
        cell_total.setCellStyle(style_total);
        cell_total.setCellValue("总电费");
        
        cell_total = totalRow.createCell(2);
        cell_total.setCellStyle(style_total);
        cell_total.setCellValue("总服务费");
        
        cell_total = totalRow.createCell(3);
        cell_total.setCellStyle(style_total);
        cell_total.setCellValue("总费用");
        
        HSSFRow totalRow1 = sheetTotal.createRow(1);
        HSSFCell cell_total1 = totalRow1.createCell(0);
        cell_total1.setCellStyle(cellStyle_0);
        cell_total1.setCellValue(allTotalChargingKWH);
        
        cell_total1 = totalRow1.createCell(1);
        cell_total1.setCellStyle(style_total);
        cell_total1.setCellValue(totalChargingPrice);
        
        cell_total1 = totalRow1.createCell(2);
        cell_total1.setCellStyle(cellStyle_4);
        cell_total1.setCellValue(totalServicePrice);
        
        cell_total1 = totalRow1.createCell(3);
        cell_total1.setCellStyle(style_total);
        cell_total1.setCellValue(totalPrice);
        
        List<LineBalance> lineList = new ArrayList<LineBalance>();
        int listLength = list.size();
        for(LineBalance lineBalance : list) {
        	if(listLength == list.indexOf(lineBalance) + 1) {
        		lineList.add(lineBalance);
    			Collection<LineBalance> dataset = lineList;
    			String title = lineList.get(0).getSubBranchCompanyName() + lineList.get(0).getLineName() + "路结算明细表";
    	        // 生成一个表格
    	        HSSFSheet sheet = workbook.createSheet(lineList.get(0).getLineName() + lineList.get(0).getChargingStationName());
    	        
    	        sheet.setColumnWidth(0, 1200);
    	        sheet.setColumnWidth(1, 3200);
    	        sheet.setColumnWidth(2, 4800);
    	        sheet.setColumnWidth(3, 2800);
    	        sheet.setColumnWidth(4, 2000);
    	        sheet.setColumnWidth(5, 2400);
    	        sheet.setColumnWidth(6, 2000);
    	        sheet.setColumnWidth(7, 3500);
    	        sheet.setColumnWidth(8, 2000);
    	        sheet.setColumnWidth(9, 3500);
    	        sheet.setColumnWidth(10, 3200);
    	        sheet.setColumnWidth(11, 2000);
    	        sheet.setColumnWidth(12, 3500);
    	        sheet.setColumnWidth(13, 3500);
    	        // 纸张大小、方向
    	        HSSFPrintSetup ps = sheet.getPrintSetup();
    	        ps.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)
    	        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型
    	        sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.0); // 上边距
    	        sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.0 ); // 下边距
    	        sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.0 ); // 左边距
    	        sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.0 ); // 右边距
    	        
    	        HSSFFooter footer = sheet.getFooter();
    	        footer.setCenter(HSSFFooter.page() + "/ " + HSSFFooter.numPages() + "页");

    	        // 产生title  
    	        HSSFRow row_title = sheet.createRow(0);
    	        for (int i = 0; i < headers.length; i++) {
    	        	HSSFCell cell = row_title.createCell(i);  
    	            cell.setCellStyle(style_title);
    			}
    	        Cell cell_title = row_title.createCell(0);
    	        cell_title.setCellStyle(style_title);
    	        cell_title.setCellValue(title);
    	        // 合并单元格
    	        CellRangeAddress cra_title = new CellRangeAddress(0, 0, 0, headers.length - 1);
    	        //在sheet里增加合并单元格  
    	        sheet.addMergedRegion(cra_title);
    	        
    	        HSSFRow row_danwei = sheet.createRow(1);
    	        HSSFCell cell_danwei = row_danwei.createCell(headers.length - 4);
    	        cell_danwei.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_RIGHT);
    	        cell_danwei.getCellStyle().setFont(font_data);
    	        cell_danwei.getCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	        cell_danwei.setCellValue("单位:元");
    	        sheet.addMergedRegion(new CellRangeAddress(1,1,headers.length - 4,headers.length - 1));
    	        
    	        // 产生标题行
    	        HSSFRow row_header = sheet.createRow(2);
    	        for (short i = 0; i < headers.length; i++) { 
    	            HSSFCell cell = row_header.createCell(i);  
    	            cell.setCellStyle(style);  
    	            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
    	            cell.setCellValue(text);
    	        }
    	        
    	        HSSFCellStyle cellStyle = workbook.createCellStyle();  
    	        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
    	        // 设置这些样式  
    	        cellStyle.setFillForegroundColor(HSSFColor.AUTOMATIC.index);  
    	        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
    	        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
    	        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
    	        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
    	        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
    	        // 设置自动换行
    	        cellStyle.setWrapText(true);
    	        // 设置文字水平居中
    	        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	        // 设置文字垂直居中
    	        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	        cellStyle.setFont(font_data);
    	        sheet.createFreezePane(0, 3, 0, 3);
    	        // 遍历集合数据，产生数据行  
    	        Iterator<LineBalance> it = dataset.iterator();
    	        int index = 2;
    	        String effectiveDate = null;
    	        int startRow = 3;
    	        int rowNo = 0;
    	        int count = 1;
				
    	        while (it.hasNext()){
    	            int cellIndex = 0;
    	        	index++;
    	        	rowNo++;
    	        	HSSFRow row = sheet.createRow(index);
    	        	HSSFCell cell_No = row.createCell(0);
    	        	cell_No.setCellStyle(style_data);
    	        	cell_No.setCellValue(rowNo);
    	        	LineBalance t = (LineBalance) it.next();
    	        	// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
    	            Field[] fields = t.getClass().getDeclaredFields();
    	            for (int i = 0; i < fields.length; i++) {
    	            	cellIndex++;
    	                Field field = fields[i];  
    	                String fieldName = field.getName();  
    	                String getMethodName = "get"  + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
    	                if ("getStartChargingDTTM".equals(getMethodName)) {
    						break;
    					} else if ("getEffectiveDTTM".equals(getMethodName)) {
    						cellIndex--;
    						Class tCls = t.getClass();  
    		                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
    		                Object value = getMethod.invoke(t, new Object[]{});
    						if (effectiveDate == null) {
    							effectiveDate = value.toString();
    						} else {
								count++;
    						}
    					} else {
    		            	HSSFCell cell = row.createCell(cellIndex);
    		            	cell.setCellStyle(style_data);  
    						Class tCls = t.getClass();  
    		                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
    		                Object value = getMethod.invoke(t, new Object[]{});
    		                // 单元格赋值
    		                setValue(value, cell, workbook, getMethodName, cellStyle, font_data, cellStyle1, cellStyle_0, cellStyle_2, cellStyle_4);
    					}
    				}
    	        }

    	        HSSFRow row_total = sheet.createRow(index + 1);
    	        HSSFCell cell = row_total.createCell(0);
	        	cell.setCellStyle(style_data);
	        	cell.setCellValue("累 计");
    	        for (int j = 1; j < 5; j++) {
    	        	cell = row_total.createCell(j);
    	        	cell.setCellStyle(style_data);
    			}
    	        sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0, 4));

    	        cell = row_total.createCell(5);
    			cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    	        cell.setCellStyle(cellStyle_0);
    	        cell.setCellFormula("SUM(F" + String.valueOf(startRow + 1) + ":F" + String.valueOf(startRow + count) + ")");
    	        
    	        cell = row_total.createCell(6);
    	        cell.setCellStyle(style_data);
    	        cell.setCellValue("——");
    	        
    	        cell = row_total.createCell(7);
    	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    	        cell.setCellStyle(cellStyle_4);
    	        cell.setCellFormula("SUM(H" + String.valueOf(startRow + 1) + ":H" + String.valueOf(startRow + count) + ")");
    	        
    	        cell = row_total.createCell(8);
    	        cell.setCellStyle(style_data);
    	        cell.setCellValue("——");
    	        
    	        cell = row_total.createCell(9);
    	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    	        cell.setCellStyle(cellStyle_4);
    	        cell.setCellFormula("SUM(J" + String.valueOf(startRow + 1) + ":J" + String.valueOf(startRow + count) + ")");
    	        
    	        cell = row_total.createCell(10);
    	        cell.setCellStyle(style_data);
    	        cell.setCellValue("——");
    	        
    	        cell = row_total.createCell(11);
    	        cell.setCellStyle(style_data);
    	        cell.setCellValue("——");
    	        
    	        cell = row_total.createCell(12);
    	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    	        cell.setCellStyle(cellStyle_4);
    	        cell.setCellFormula("SUM(M" + String.valueOf(startRow + 1) + ":M" + String.valueOf(startRow + count) + ")");
    	        
    	        cell = row_total.createCell(13);
    	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
    	        cell.setCellStyle(cellStyle_4);
    	        cell.setCellFormula("SUM(J" + (index + 2) + "+M" + (index + 2) + ")");
    			lineList.clear();
    		
        	} else {
        		if(lineBalance.getLineName().equals(list.get(list.indexOf(lineBalance) + 1).getLineName()) && lineBalance.getChargingStationName().equals(list.get(list.indexOf(lineBalance) + 1).getChargingStationName())) {
        			lineList.add(lineBalance);
        		} else {
        			lineList.add(lineBalance);
        			
        			Collection<LineBalance> dataset = lineList;
        			String title = lineList.get(0).getSubBranchCompanyName() + lineList.get(0).getLineName() + "路结算明细表";
        	        // 生成一个表格
        	        HSSFSheet sheet = workbook.createSheet(lineList.get(0).getLineName() + lineList.get(0).getChargingStationName());
        	        // 纸张大小、方向

        	        sheet.setColumnWidth(0, 1200);
        	        sheet.setColumnWidth(1, 3200);
        	        sheet.setColumnWidth(2, 4800);
        	        sheet.setColumnWidth(3, 2800);
        	        sheet.setColumnWidth(4, 2000);
        	        sheet.setColumnWidth(5, 2400);
        	        sheet.setColumnWidth(6, 2000);
        	        sheet.setColumnWidth(7, 3500);
        	        sheet.setColumnWidth(8, 2000);
        	        sheet.setColumnWidth(9, 3500);
        	        sheet.setColumnWidth(10, 3200);
        	        sheet.setColumnWidth(11, 2000);
        	        sheet.setColumnWidth(12, 3500);
        	        sheet.setColumnWidth(13, 3500);
        	        HSSFPrintSetup ps = sheet.getPrintSetup();
        	        ps.setLandscape(true); // 打印方向，true：横向，false：纵向(默认)
        	        ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); //纸张类型
        	        sheet.setMargin(HSSFSheet.TopMargin,( double ) 0.0); // 上边距
        	        sheet.setMargin(HSSFSheet.BottomMargin,( double ) 0.0 ); // 下边距
        	        sheet.setMargin(HSSFSheet.LeftMargin,( double ) 0.0 ); // 左边距
        	        sheet.setMargin(HSSFSheet.RightMargin,( double ) 0.0 ); // 右边距
        	        
        	        HSSFFooter footer = sheet.getFooter();
        	        footer.setCenter(HSSFFooter.page() + "/ " + HSSFFooter.numPages() + "页");
        	        // 产生title  
        	        HSSFRow row_title = sheet.createRow(0);
        	        for (int i = 0; i < headers.length; i++) {
        	        	HSSFCell cell = row_title.createCell(i);  
        	            cell.setCellStyle(style_title);
        			}
        	        Cell cell_title = row_title.createCell(0);
        	        cell_title.setCellStyle(style_title);
        	        cell_title.setCellValue(title);
        	        // 合并单元格
        	        CellRangeAddress cra_title = new CellRangeAddress(0, 0, 0, headers.length - 1);
        	        //在sheet里增加合并单元格  
        	        sheet.addMergedRegion(cra_title);
        	        
        	        HSSFRow row_danwei = sheet.createRow(1);
        	        HSSFCell cell_danwei = row_danwei.createCell(headers.length - 4);
        	        cell_danwei.getCellStyle().setAlignment(HSSFCellStyle.ALIGN_RIGHT);
        	        cell_danwei.getCellStyle().setFont(font_data);
        	        cell_danwei.getCellStyle().setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        	        cell_danwei.setCellValue("单位:元");
        	        sheet.addMergedRegion(new CellRangeAddress(1,1,headers.length - 4,headers.length - 1));
        	        
        	        // 产生标题行
        	        HSSFRow row_header = sheet.createRow(2);
        	        for (short i = 0; i < headers.length; i++) { 
        	            HSSFCell cell = row_header.createCell(i);  
        	            cell.setCellStyle(style);  
        	            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
        	            cell.setCellValue(text);
        	        }
        	        
        	        HSSFCellStyle cellStyle = workbook.createCellStyle();  
        	        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        	        // 设置这些样式  
        	        cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);  
        	        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        	        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
        	        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
        	        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);  
        	        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        	        // 设置自动换行
        	        cellStyle.setWrapText(true);
        	        // 设置文字水平居中
        	        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        	        // 设置文字垂直居中
        	        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        	        cellStyle.setFont(font_data);
        	        sheet.createFreezePane(0, 3, 0, 3);
        	        // 遍历集合数据，产生数据行  
        	        Iterator<LineBalance> it = dataset.iterator();
        	        int index = 2;
        	        String effectiveDate = null;
        	        int startRow = 3;
        	        int rowNo = 0;
        	        int count = 1;
    				
        	        while (it.hasNext()){
        	            int cellIndex = 0;
        	        	index++;
        	        	rowNo++;
        	        	HSSFRow row = sheet.createRow(index);
        	        	HSSFCell cell_No = row.createCell(0);
        	        	cell_No.setCellStyle(style_data);
        	        	cell_No.setCellValue(rowNo);
        	        	LineBalance t = (LineBalance) it.next();
        	        	// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值  
        	            Field[] fields = t.getClass().getDeclaredFields();
        	            for (int i = 0; i < fields.length; i++) {
        	            	cellIndex++;
        	                Field field = fields[i];  
        	                String fieldName = field.getName();  
        	                String getMethodName = "get"  + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
        	                if ("getStartChargingDTTM".equals(getMethodName)) {
        						break;
        					} else if ("getEffectiveDTTM".equals(getMethodName)) {
        						cellIndex--;
        						Class tCls = t.getClass();  
        		                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
        		                Object value = getMethod.invoke(t, new Object[]{});
        						if (effectiveDate == null) {
        							effectiveDate = value.toString();
        						} else {
    								count++;
        						}
        					} else {
        		            	HSSFCell cell = row.createCell(cellIndex);
        		            	cell.setCellStyle(style_data);  
        						Class tCls = t.getClass();  
        		                Method getMethod = tCls.getMethod(getMethodName, new Class[]{});  
        		                Object value = getMethod.invoke(t, new Object[]{});
        		                // 单元格赋值
        		                setValue(value, cell, workbook, getMethodName, cellStyle, font_data, cellStyle1, cellStyle_0, cellStyle_2, cellStyle_4);
        					}
        				}
        	        }
        	        HSSFRow row_total = sheet.createRow(index + 1);
        	        HSSFCell cell = row_total.createCell(0);
    	        	cell.setCellStyle(style_data);
    	        	cell.setCellValue("累 计");
        	        for (int j = 1; j < 5; j++) {
        	        	cell = row_total.createCell(j);
        	        	cell.setCellStyle(style_data);
        			}
        	        sheet.addMergedRegion(new CellRangeAddress(index + 1, index + 1, 0, 4));

        	        cell = row_total.createCell(5);
        			cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        	        cell.setCellStyle(cellStyle_0);
        	        cell.setCellFormula("SUM(F" + String.valueOf(startRow + 1) + ":F" + String.valueOf(startRow + count) + ")");
        	        
        	        cell = row_total.createCell(6);
        	        cell.setCellStyle(style_data);
        	        cell.setCellValue("——");
        	        
        	        cell = row_total.createCell(7);
        	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        	        cell.setCellStyle(cellStyle_4);
        	        cell.setCellFormula("SUM(H" + String.valueOf(startRow + 1) + ":H" + String.valueOf(startRow + count) + ")");
        	        
        	        cell = row_total.createCell(8);
        	        cell.setCellStyle(style_data);
        	        cell.setCellValue("——");
        	        
        	        cell = row_total.createCell(9);
        	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        	        cell.setCellStyle(cellStyle_4);
        	        cell.setCellFormula("SUM(J" + String.valueOf(startRow + 1) + ":J" + String.valueOf(startRow + count) + ")");
        	        
        	        cell = row_total.createCell(10);
        	        cell.setCellStyle(style_data);
        	        cell.setCellValue("——");
        	        
        	        cell = row_total.createCell(11);
        	        cell.setCellStyle(style_data);
        	        cell.setCellValue("——");
        	        
        	        cell = row_total.createCell(12);
        	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        	        cell.setCellStyle(cellStyle_4);
        	        cell.setCellFormula("SUM(M" + String.valueOf(startRow + 1) + ":M" + String.valueOf(startRow + count) + ")");
        	        
        	        cell = row_total.createCell(13);
        	        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        	        cell.setCellStyle(cellStyle_4);
        	        cell.setCellFormula("SUM(J" + String.valueOf(index + 2) + "+M" + String.valueOf(index + 2) + ")");
        	        
        			lineList.clear();
        		}
        	}
        }
        
        workbook.write(servletOutputStream);
        servletOutputStream.flush();
        servletOutputStream.close();
    }

	private void setValue(Object value, HSSFCell cell, HSSFWorkbook workbook, String getMethodName, HSSFCellStyle cellStyle, HSSFFont font_data, HSSFCellStyle cellStyle1, HSSFCellStyle cellStyle_0, HSSFCellStyle cellStyle_2, HSSFCellStyle cellStyle_4) {
		if (value instanceof Integer) {
			int intValue = (Integer) value;
			cell.setCellValue(intValue);
		} else if (value instanceof Double) {
			double dValue = (double) value;
			cell.setCellValue(dValue);
			if ("getTotalChargingKWH".equals(getMethodName)) {
	            cell.setCellStyle(cellStyle_0);
			} else if ("getOilPrice".equals(getMethodName)) {
				cell.setCellStyle(cellStyle_2);
			} else {
				cell.setCellValue(dValue);
				cell.setCellStyle(cellStyle_4);
			}
		} else if (value instanceof String) {
			String strValue = value.toString();
			cell.setCellValue(strValue);
		} else if (value instanceof Date) {
			Date date = (Date) value; 
			cell.setCellValue(date);
			cell.setCellStyle(cellStyle1);
		}
	}
	
//	public double round(double value) {
//		BigDecimal b = new BigDecimal(Double.toString(value));
//		return b.setScale(4, RoundingMode.DOWN).doubleValue();
//	}
	
    public int stringNumbers(String str, String des) {
    	int counter = 0;
    	while (str.indexOf(des) != -1) {
    		counter++;
    		str = str.substring(str.indexOf(des) + des.length());
    	}
        return counter;
    }
}